alter proc rpt_AI_AccountBalance
@date datetime
as
begin
	declare @firstDate as datetime
	set @firstDate = DATEADD(mm, DATEDIFF(mm, 0, @date), 0)
	declare @lastDate as datetime
	set @lastDate = dateadd(dd,-1,dateadd(mm,datediff(mm,0,@date)+1,0))
	
	declare @firstLM as datetime
	set @firstLM = DATEADD(MM, -1, @firstDate)
	declare @lastLM as datetime
	set @lastLM = DATEADD(MM, -1, @lastDate)
	
	declare @table table
	(
		AcctCode nvarchar(100),
		AcctName nvarchar(100),
		AcctCodeL1 nvarchar(100),
		AcctNameL1 nvarchar(100),
		AcctCodeL2 nvarchar(100),
		AcctNameL2 nvarchar(100),
		AcctCodeL3 nvarchar(100),
		AcctNameL3 nvarchar(100),
		AcctCodeL4 nvarchar(100),
		AcctNameL4 nvarchar(100)
	)
	insert into @table 
	select ac.AcctCode, ac.AcctName,
		 ac1.AcctCode AcctCodeL1, ac1.AcctName AcctNameL1,
		 ac2.AcctCode AcctCodeL2, ac2.AcctName AcctNameL2,
		 ac3.AcctCode AcctCodeL3, ac3.AcctName AcctNameL3,
		 ac4.AcctCode AcctCodeL4, ac4.AcctName AcctNameL4 
	from  
		OACT ac 
		join OACT ac1 on ac.AcctCode = ac1.FatherNum
		join OACT ac2 on ac1.AcctCode = ac2.FatherNum
		join OACT ac3 on ac2.AcctCode = ac3.FatherNum
		join OACT ac4 on ac3.AcctCode = ac4.FatherNum
	where ac.Levels = 1 and ac1.Levels = 2
		and ac2.Levels = 3 and ac3.Levels = 4
		and ac4.Levels = 5 and ac4.GroupMask in (1,2,3)
	
	select AcctCode,
		AcctName,
		AcctCodeL1,
		AcctNameL1,
		AcctCodeL2,
		AcctNameL2,
		AcctCodeL3,
		AcctNameL3,
		AcctCodeL4,
		AcctNameL4, 
		SUM(isnull(T.curAmt, 0)) curAmt, Sum(isnull(T.prevAmt, 0))prevAmt
	from @table tab
	left join 
	(
	select jd.Account, jd.Debit-jd.Credit curAmt, 0 prevAmt from JDT1 jd where jd.RefDate between @firstDate and @lastDate
	union 
	select jd.Account, 0 curAmt, jd.Debit-jd.Credit prevAmt from JDT1 jd where jd.RefDate between @firstLM and @lastLM
	)T on tab.AcctCodeL4 = T.Account collate SQL_Latin1_General_CP850_CI_AS
	group by AcctCode,
		AcctName,
		AcctCodeL1,
		AcctNameL1,
		AcctCodeL2,
		AcctNameL2,
		AcctCodeL3,
		AcctNameL3,
		AcctCodeL4,
		AcctNameL4
	
	
end

--rpt_AI_AccountBalance '01-01-2013'